package implicits

import java.text.SimpleDateFormat
import org.apache.commons.lang3.StringUtils
import org.apache.poi.ss.usermodel.{Cell, CellType, DateUtil}
import org.apache.poi.xssf.usermodel.{XSSFCell, XSSFColor, XSSFFormulaEvaluator, XSSFWorkbook}

import scala.collection.JavaConverters._
import implicits.Implicits._

import java.io.{File, FileInputStream}

/**
 * Created by Administrator on 2019/11/11
 */

trait MyXlxsTool {

  implicit class MyXlsxPath(file: File) {

    def getCellValue(cellType: CellType, cell: XSSFCell, formulaEvaluator: XSSFFormulaEvaluator): String = {
      cellType match {
        case CellType.STRING =>
          cell.getStringCellValue.replaceAll("\t"," ")
        case CellType.NUMERIC =>
          if (DateUtil.isCellDateFormatted(cell)) {
            val dateFormat = new SimpleDateFormat("yyyy/MM/dd")
            dateFormat.format(cell.getDateCellValue)
          } else {
            val doubleValue = cell.getNumericCellValue
            if (doubleValue == doubleValue.toInt) {
              doubleValue.toInt.toString
            } else doubleValue.toString
          }
        case CellType.FORMULA =>
          val resultCellType = formulaEvaluator.evaluate(cell).getCellTypeEnum
          getCellValue(resultCellType, cell, formulaEvaluator)
        case _ =>
          ""
      }
    }

    def xlsxLines(sheetIndex: Int = 0, colorOp: Option[String] = None) = {
      val is = new FileInputStream(file.getAbsolutePath)
      val xssfWorkbook = new XSSFWorkbook(is)
      val formulaEvaluator = xssfWorkbook.getCreationHelper.createFormulaEvaluator()
      val xssfSheet = xssfWorkbook.getSheetAt(sheetIndex)
      val firstRow = xssfSheet.getRow(0)
      val firstRowIndexs = (0 until firstRow.getLastCellNum).filter { j =>
        colorOp.map { color =>
          val cell = firstRow.getCell(j)
          val xssfColor = cell.getCellStyle.getFillForegroundXSSFColor
          xssfColor != null && xssfColor.getARGBHex == s"FF${color.tail}"
        }.getOrElse(true)
      }
      val lines = (0 to xssfSheet.getLastRowNum).toList.map { i =>
        xssfSheet.getRow(i)
      }.filter { xssfRow =>
        xssfRow != null
      }.map { xssfRow =>
        firstRowIndexs.map { j =>
          val cell = xssfRow.getCell(j)
          val value = if (cell == null) {
            ""
          } else {
            val cellType = cell.getCellTypeEnum
            getCellValue(cellType, cell, formulaEvaluator)
          }
          value.trim
        }.toList
      }
      xssfWorkbook.close()
      val rs = lines.filter(x => x.exists(y => StringUtils.isNotBlank(y)))
      rs
    }


  }


}


